﻿/********************************************************
 * 类名： MenuDAL.cs
 * 功能： 菜单数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2013-11-27	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using PSS.Entity.SystemManage;
using PSS.Utility;

namespace PSS.DAL.SystemManage
{
    public class MenuDAL : DbHelperBase
    {
        public IList<MenuEntity> LoadMenuList(string loginCode)
        {
            var strSql = new StringBuilder();
            var list = new List<MenuEntity>();
            object obj = DBNull.Value;
            strSql.Append(" SELECT A.MenuId,A.MenuName,A.Url,A.ParentMenuId FROM PSS_Menu AS A ");
            //strSql.Append(" INNER JOIN UT_RoleRight AS B ON A.MenuId = B.MenuId  ");
            //strSql.Append(" INNER JOIN UT_Role AS C ON B.RoleID = C.RoleID ");
            //strSql.Append(" INNER JOIN UT_User AS D ON C.RoleID = D.RoleID ");
            //strSql.Append(" WHERE D.LoginCode=@LoginCode ");
            strSql.Append(" ORDER BY A.[MenuIdx] ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    //Db.AddInParameter(cmd, "@LoginCode", loginCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new MenuEntity();
                            if (reader["MenuId"] != DBNull.Value)
                                model.MenuId = new Guid(reader["MenuId"].ToString());
                            if (reader["ParentMenuId"] != DBNull.Value)
                                model.ParentMenuId = new Guid(reader["ParentMenuId"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.Url = reader["Url"] == obj ? null : Convert.ToString(reader["Url"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<MenuEntity> GetMenuParentNode()
        {
            var list = new List<MenuEntity>();
            object obj = DBNull.Value;
            const string strSql = @"SELECT [MenuId],[MenuName],[ParentMenuId] FROM PSS_Menu where Flag=0  ORDER BY [MenuIdx] asc  ";
            using (var cmd = Db.GetSqlStringCommond(strSql))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new MenuEntity
                            {
                                MenuId = new Guid(reader["MenuId"].ToString()),
                                MenuName =
                                    reader["MenuName"] == obj
                                        ? null
                                        : Convert.ToString(reader["MenuName"])
                            };
                            if (reader["ParentMenuId"] != DBNull.Value)
                                model.ParentMenuId = new Guid(reader["ParentMenuId"].ToString());
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetTotal(string menuId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(*) AS [Total] FROM PSS_Menu where ParentMenuId=@MenuId");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", menuId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<MenuEntity> GetMenuChildNode(MenuQcEntity qcmodel)
        {
            var list = new List<MenuEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT Tmp.* From ( ");
            strSql.Append(" SELECT row_number() over (order by MenuIdx) as idx,[MenuId],[MenuName],[ParentMenuId],[Url],[MenuIdx],[CreateTime] FROM [PSS_Menu] ");
            strSql.Append(" WHERE ParentMenuId=@ParentMenuId ) Tmp ");
            strSql.Append(" Where Tmp.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ParentMenuId", qcmodel.MenuId);

                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new MenuEntity();
                            if (reader["MenuId"] != DBNull.Value)
                                model.MenuId = new Guid(reader["MenuId"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.Url = reader["Url"] == obj ? null : Convert.ToString(reader["Url"]);
                            if (reader["ParentMenuId"] != DBNull.Value)
                                model.ParentMenuId = new Guid(reader["ParentMenuId"].ToString());
                            if (reader["MenuIdx"] != DBNull.Value)
                                model.MenuIdx = Convert.ToInt32(reader["MenuIdx"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public MenuEntity GetMenuInfo(string id)
        {
            var model = new MenuEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [MenuId],[MenuName],[ParentMenuId],[Url],[MenuIdX] FROM [PSS_Menu] where MenuId=@MenuId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["MenuId"] != DBNull.Value)
                                model.MenuId = new Guid(reader["MenuId"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.Url = reader["Url"] == obj ? null : Convert.ToString(reader["Url"]);
                            if (reader["ParentMenuId"] != DBNull.Value)
                                model.ParentMenuId = new Guid(reader["ParentMenuId"].ToString());
                            if (reader["MenuIdx"] != DBNull.Value)
                                model.MenuIdx = Convert.ToInt32(reader["MenuIdx"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool AddMenu(MenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_Menu]([MenuId],[MenuName],[Url],[ParentMenuId],[MenuIdx],[Flag]) ");
            strSql.Append(" VALUES(@MenuId,@MenuName,@Url,@ParentMenuId,@MenuIdx,@Flag) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", model.MenuId);
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@Url", model.Url);
                    Db.AddInParameter(cmd, "@ParentMenuId", model.ParentMenuId);
                    Db.AddInParameter(cmd, "@MenuIdx", model.MenuIdx);
                    Db.AddInParameter(cmd, "@Flag", model.Flag);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyMenu(MenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Menu] SET [MenuName] = @MenuName,[Url] = @Url,[MenuIdx] = @MenuIdx WHERE [MenuId] = @MenuId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@Url", model.Url);
                    Db.AddInParameter(cmd, "@MenuIdx", model.MenuIdx);
                    Db.AddInParameter(cmd, "@MenuId", model.MenuId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Remove(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From PSS_Menu Where MenuId=@MenuId ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", id);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveChildMenuNode(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From PSS_Menu Where ParentMenuId=@MenuId ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", id);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool AddParentMenuNode(MenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            if (model.MenuId == Guid.Empty)
            {
                strSql.Append(" INSERT INTO [PSS_Menu]([MenuId],[MenuName],[MenuIdx])");
                strSql.Append(" VALUES(@MenuId,@MenuName,@MenuIdx) ");
            }
            else
            {
                strSql.Append(" INSERT INTO [PSS_Menu]([MenuId],[MenuName],[ParentMenuId],[Flag],[MenuIdx])");
                strSql.Append(" VALUES(@MenuId,@MenuName,@ParentMenuId,@Flag,@MenuIdx) ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuId", model.MenuId);
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@MenuIdx", model.MenuIdx);
                    if (model.MenuId != Guid.Empty)
                    {
                        Db.AddInParameter(cmd, "@ParentMenuId", model.ParentMenuId);
                        Db.AddInParameter(cmd, "@Flag", model.Flag);
                    }
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyParentMenuNode(MenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Menu] SET [MenuName] = @MenuName,[MenuIdx] = @MenuIdx WHERE [MenuId] = @MenuId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@MenuIdx", model.MenuIdx);
                    Db.AddInParameter(cmd, "@MenuId", model.MenuId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public IList<MenuEntity> GetMenuList()
        {
            var strSql = new StringBuilder();
            var list = new List<MenuEntity>();
            object obj = DBNull.Value;
            strSql.Append("SELECT MenuId,MenuName,Url,ParentMenuId FROM PSS_Menu");
            strSql.Append("  ORDER BY [MenuIdx] ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new MenuEntity();
                            if (reader["MenuId"] != DBNull.Value)
                                model.MenuId = new Guid(reader["MenuId"].ToString());
                            if (reader["ParentMenuId"] != DBNull.Value)
                                model.ParentMenuId = new Guid(reader["ParentMenuId"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
